/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package researchgrants.parts.GrantRequest.perennialStipends;

import researchgrants.parts.GrantRequest.agency.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import researchgrants.parts.LoggedData.LoggedData;
import researchgrants.parts.db.Db;

/**
 * The underlying PerennialStipends which is a field (expectedPerennialStipends) in GrantRequest and a field (actualPerennialStipends) in GrantRequestStatusChangeAwarded.
 * This class has a years count (years field) and an array of Stipends for each year
 * @author DOStudent1
 */
public class PerennialStipends {
   public static PerennialStipends getByID(int id) {
        return(new PerennialStipends(id));
    }

    // the fields values
    private int id;
    private LoggedData years;
    private Stipend[] stipends;
    
    private PerennialStipends(int id) {
        this.id=id;
    }

    // This does the neccessary changes when the year count changes. This means resizing the yearly values with the appropriate database operations (add new, delete old ones not needed)
    // The years object contains the new value before issuing a call to this function.
    void setYears(int newYears) {
        syncDataFromDb();
        try {
            // retreives how many years are physically available
            Connection conn = Db.openDbConnection();
            PreparedStatement currPhysicalYearsPrepareStatement = Db.createPreparedStatement(conn, "SELECT max(YearIndex) as MaxYears FROM tblStipends WHERE PerennialStipendsID=?");
            currPhysicalYearsPrepareStatement.setInt(1, id);
            ResultSet rs = currPhysicalYearsPrepareStatement.executeQuery();
            int currPhysicalYears;
            if (!rs.next()) {
                currPhysicalYears=0;
            } else {
                if (rs==null) {
                    currPhysicalYears=0;
                } else {
                    Object column=Db.getColumn(rs, 1);
                    if (column==null) {
                        currPhysicalYears=0;
                    } else {
                        currPhysicalYears=(Integer)column;
                    }
                }
            }
            rs.close();
            currPhysicalYearsPrepareStatement.close();
            
            PreparedStatement insertStipendPreparedStatement=conn.prepareCall("INSERT INTO TblStipends (PerennialStipendsID, YearIndex, DirectMoneyRef, IndirectMoneyRef, Active) VALUES (?, ?, ?, ?, 1)");
            insertStipendPreparedStatement.setInt(1, id);

            // only if the new years requested is bigger than the actual years in the database (upon increasing the year count), create new stipend records for it.
            for (int i=currPhysicalYears+1; i<=newYears; i++) {
                LoggedData newDirectMoneyLoggedData=LoggedData.createNew("");
                LoggedData newIndirectMoneyLoggedData=LoggedData.createNew("");
                insertStipendPreparedStatement.setInt(2, i);
                insertStipendPreparedStatement.setInt(3, newDirectMoneyLoggedData.getId());
                insertStipendPreparedStatement.setInt(4, newIndirectMoneyLoggedData.getId());
                insertStipendPreparedStatement.executeUpdate();
            }
            
            insertStipendPreparedStatement.close();

            // Activate the needed years
            PreparedStatement updateActiveStipendsPreparedStatement=conn.prepareCall("UPDATE TblStipends SET Active=1 WHERE PerennialStipendsID=? AND YearIndex<=?");
            updateActiveStipendsPreparedStatement.setInt(1, id);
            updateActiveStipendsPreparedStatement.setInt(2, newYears);
            updateActiveStipendsPreparedStatement.executeUpdate();
            updateActiveStipendsPreparedStatement.close();
            
            // Deactivate the not needed years (upon decreasing the year count)
            PreparedStatement updateDeactiveStipendsPreparedStatement=conn.prepareCall("UPDATE TblStipends SET Active=0 WHERE PerennialStipendsID=? AND YearIndex>?");
            updateDeactiveStipendsPreparedStatement.setInt(1, id);
            updateDeactiveStipendsPreparedStatement.setInt(2, newYears);
            updateDeactiveStipendsPreparedStatement.executeUpdate();
            updateDeactiveStipendsPreparedStatement.close();
            
            Db.closeDbConnection(conn);
            
        } catch (SQLException ex) {
            Logger.getLogger(PerennialStipends.class.getName()).log(Level.SEVERE, null, ex);
        } catch (Exception ex) {
            Logger.getLogger(PerennialStipends.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        // after manually adding the new records, reload from the database 
        syncDataFromDb();
        
    }

    // retreives the content from the database - not upon creation but upon request of data
    private void syncDataFromDb() {
        try {
            Connection conn = Db.openDbConnection();
            PreparedStatement prepareStatement = Db.createPreparedStatement(conn, "SELECT YearsRef FROM tblPerennialStipends WHERE PerennialStipendsID=?");
            prepareStatement.setInt(1, id);
            ResultSet rs = prepareStatement.executeQuery();
            Db.moveToFirstLine(rs);

            // loads the year count
            years=LoggedData.getByID((Integer) Db.getColumn(rs, 1));
            int yearsInt=0;
            if (!years.value().equals("")) {
                yearsInt=Integer.parseInt(years.value());
            }

            // loads all the years
            stipends=new Stipend[yearsInt];
            for (int i=0;i<yearsInt;i++) {
                stipends[i]=Stipend.getByID(id, i+1);
            }
            
            rs.close();
            prepareStatement.close();
            Db.closeDbConnection(conn);
        } catch (SQLException ex) {
            Logger.getLogger(Agency.class.getName()).log(Level.SEVERE, null, ex);
        } catch (Exception ex) {
            Logger.getLogger(Agency.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    ///////////////////////////
    // Retreives the fields
    ///////////////////////////
    
    public String getCurrentYears() {
        syncDataFromDb();
        return(years.value());
    }

    public Stipend[] getStipends() {
        syncDataFromDb();
        return(stipends);
    }

    public int getId() {
        return(id);
    }

    ///////////////////////////////////////////////////
    // Retreives the raw fields - used for the panels
    ///////////////////////////////////////////////////
    
    LoggedData getLoggedDataYears() {
        syncDataFromDb();
        return years;
    }

}
